import 'package:logger/logger.dart';
import 'package:qgchat/http/apis/user.dart';
import 'package:qgchat/http/models/LocalTalkRecordModel.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';

class Sql {
  // 单例
  static final Sql _instance = new Sql._internal();
  factory Sql() => _instance;
  Sql._internal();

  /// 数据库
  Database db;

  /// 数据库是否初始化完成
  bool get isReady => db != null && db.isOpen;

  /// 数据表名
  final String tableName = "talk";

  /// 初始化数据库
  Future<Database> init() async {
    String databasesPath = await getDatabasesPath();
    String path = join(databasesPath, tableName);
    return await openDatabase(
      path,
      version: 1,
      onOpen: (database) => db = database,
      onCreate: (Database database, int version) async {
        await database.execute(
          '''
          CREATE TABLE IF NOT EXISTS $tableName (
            id INTEGER NOT NULL,
            from_id VARCHAR(50) NOT NULL,
            to_id VARCHAR(50) NOT NULL,
            text VARCHAR(255) NOT NULL,
            createtime INT(11) NOT NULL,
            member_nickname VARCHAR(32),
            member_head VARCHAR(50),
            read INT(1) DEFAULT 0
          )
          ''',
        );
      },
    );
  }

  /// 清空数据(测试用，慎用)
  clear() async {
    await db.delete(tableName);
  }

  /// 插入私聊记录
  Future<int> insert(LocalTalkRecordModel values) async {
    final res = await db.query(
      tableName,
      where: "id = ?",
      whereArgs: [values.id],
    );
    if (res.length > 0) return 0;
    return await db.insert('$tableName', values.toJson());
  }

  /// 查询所有消息
  all() async {
    final res = await db.query(tableName, orderBy: "id desc");
    // LocalTalkRecordModel.fromJson(res);
    Logger().i(res);
    return res;
  }

  /// 消息列表
  /// read字段为0说明有未读消息
  Future<List<LocalTalkRecordModel>> queryUserRecord(String token) async {
    final list = await db.rawQuery(
      '''
      SELECT *
      FROM
      (
          SELECT *, to_id AS other
          FROM `$tableName`
          WHERE from_id = "$token"
        UNION
          SELECT *, from_id AS other
          FROM `$tableName`
          WHERE to_id = "$token"
      ) AS t
      GROUP BY other
      ORDER BY id desc
      ''',
    );
    return List.generate(
      list.length,
      (i) => LocalTalkRecordModel.fromJson(list[i]),
    );
  }

  /// 对话记录
  /// 获取了对话后
  /// 自动将获取的消息
  /// 转为已读
  Future<List<LocalTalkRecordModel>> queryDialog(
      String token, String other) async {
    final list = await db.rawQuery(
      '''
      select * 
      from 
      (
        select * 
        from `$tableName` 
        where from_id = "$token" and to_id = "$other"
        union
        select * 
        from `$tableName` 
        where to_id = "$token" and from_id = "$other"
      ) as t
      group by id
      order by id
      ''',
    );
    await updateRead(token, other);
    return List.generate(
      list.length,
      (i) => LocalTalkRecordModel.fromJson(list[i]),
    );
  }

  /// 更新 点对点点私聊 红点
  Future<int> updateRead(String token, String other) async {
    return await db.rawUpdate(
      '''
      UPDATE 
      `$tableName` 
      set read = 1
      where (from_id = "$token" and to_id = "$other") or (to_id = "$token" and from_id = "$other")
      ''',
    );
  }

  /// 获取点对点聊天记录的最后一条消息的id
  Future<int> queryDialogLastId(String token, String other) async {
    final res = await db.rawQuery(
      '''
      select id
      from 
      (
        select * 
        from `$tableName` 
        where from_id = "$token" and to_id = "$other"
        union
        select * 
        from `$tableName` 
        where to_id = "$token" and from_id = "$other"
      ) as t
      group by id
      order by id desc
      limit 1
      ''',
    );
    return res.length > 0 ? res[0]['id'] : null;
  }

  /// 判断是否有红点
  Future<bool> queryUnRead(String token) async {
    final res = await db.rawQuery(
      '''
      select count(*) as count
      from 
      `$tableName`
      where read = 0 and (from_id = "$token" or to_id = "$token")
      ''',
    );
    return res.length > 0 && res[0]['count'] > 0;
  }

  /// 同步数据
  Future synchronization(String token) async {
    return await init().then((db) async {
      final arr = await UserApis.recordList(token);

      // 同步线上数据
      for (final item in arr) {
        final id = await Sql().queryDialogLastId(token, item.memberToken);
        final talks = await UserApis.talkRecord(token, item.memberToken, id: id);
        // Logger().i({
        //   "token": token,
        //   "other": item.memberToken,
        //   "id": id,
        //   "talks": talks,
        // });

        for (final talk in talks) {
          if (talk.fromId != talk.toId) {
            await Sql().insert(
              LocalTalkRecordModel.fromJson(
                {
                  "id": talk.id,
                  "from_id": talk.fromId,
                  "to_id": talk.toId,
                  "createtime": talk.createtime,
                  "text": talk.text,
                  "member_nickname": item.memberNickname,
                  "member_head": item.memberHead,
                },
              ),
            );
          }
        }
      }
    });
  }
}
